package lsy.Dao;

import lsy.entity.Course;
import lsy.entity.Teacher;
import lsy.tools.PageInfo;
import lsy.tools.PropertiesUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.apache.commons.lang3.StringUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

public class CourseDao {
    public void add(Course course) throws SQLException {
        QueryRunner queryRunner= new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "insert into course(courseId,courseName,tid) values(?,?,?)";
        queryRunner.update(sql,course.getCourseId(),course.getCourseName(),course.getTeacher().getTid());
    }
    public void delete(Integer courseId) throws SQLException{
        QueryRunner queryRunner= new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "delete from course where courseId=?";
        queryRunner.update(sql,courseId);
    }
    public void update(Course course) throws SQLException{
        QueryRunner queryRunner= new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "update course set courseName=?,tid=? where courseId =?";
        queryRunner.update(sql,course.getCourseName(),course.getTeacher().getTid(),course.getCourseId());
    }
    public PageInfo<Course> list(Course course, PageInfo<Course> pageInfo) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
        String _sql = "";
        List<Object> _list = new ArrayList<Object>();
        if(course != null && course.getCourseId() != null) {
            _sql += " and courseId = ?";
            _list.add(course.getCourseId());
        }
        if(course != null && StringUtils.isNoneBlank(course.getCourseName())) {
            _sql += " and courseName like ?";
            _list.add("%"+course.getCourseName()+"%");
        }
        if(course != null && StringUtils.isNoneBlank(course.getTeacher().getTeaName())) {
            _sql += " and teaName like ?";
            _list.add("%"+course.getTeacher().getTeaName()+"%");
        }
        if (course!=null && StringUtils.isNoneBlank(course.getTeacher().getTeaUsername())){
            _sql+="and teaUsername like ?";
            _list.add("%"+course.getTeacher().getTeaUsername()+"%");
        }
        if(course != null && course.getTeacher().getTid() != null) {
            _sql += " and course.tid  = ?";
            _list.add(course.getTeacher().getTid());
        }
        //_list转数组
        Object[] arr = new Object[_list.size()];
        for (int i=0;i<_list.size();i++) {
            arr[i] = _list.get(i);
        }
        //TODO
        String sql = "select course.*,teacher.teaName,teacher.teaUsername from course,teacher where course.tid = teacher.tid "+_sql+" limit  "+(pageInfo.getPageNo()-1)*pageInfo.getPageSize()+" , "+pageInfo.getPageSize();
        //List<Course> list = queryRunner.query(sql, new BeanListHandler<>(Course.class),arr);
        List<Map<String, Object>> Maplist = queryRunner.query(sql, new MapListHandler(),arr);
        //设置list集合
        List<Course> list = new ArrayList<>();
        //list<Map> -->list<Course>转换过程
        for (Map map :Maplist) {
            Course entity = new Course();
            entity.setCourseId(Integer.parseInt(map.get("courseId")+""));
            entity.setCourseName(map.get("courseName")+"");
            Teacher teacher = new Teacher();
            teacher.setTid(Integer.parseInt(map.get("tid")+""));
            teacher.setTeaName(map.get("teaName")+"");
            teacher.setTeaUsername(map.get("teaUsername")+"");
            entity.setTeacher(teacher);
            list.add(entity);
        }
        pageInfo.setList(list);
        pageInfo.setTotalCount(this.count(course));
        return pageInfo;
    }


    public Long count(Course course) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
        String _sql = "";
        List<Object> _list = new ArrayList<Object>();
        if(course != null && course.getCourseId() != null) {
            _sql += " and courseId = ?";
            _list.add(course.getCourseId());
        }
        if(course != null && StringUtils.isNoneBlank(course.getCourseName())) {
            _sql += " and courseName like ?";
            _list.add("%"+course.getCourseName()+"%");
        }
        if(course != null && StringUtils.isNoneBlank(course.getTeacher().getTeaName())) {
            _sql += " and teaName = ?";
            _list.add(course.getTeacher().getTeaName());
        }
        if(course != null && course.getTeacher().getTid() != null) {
            _sql += " and course.tid  = ?";
            _list.add(course.getTeacher().getTid());
        }
        //_list转数组
        Object[] arr = new Object[_list.size()];
        for (int i=0;i<_list.size();i++) {
            arr[i] = _list.get(i);
        }
        String sql = "select count(*) from course,teacher where course.tid = teacher.tid "+_sql;
        Long count = (Long)queryRunner.query(sql, new ScalarHandler(),arr);
        return count;
    }

    public Course findById(Integer courseId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "select * from course where courseId = ?";
        Map<String, Object> map = queryRunner.query(sql, new MapHandler(),courseId);
        Course course = new Course();
        course.setCourseId(Integer.parseInt(map.get("courseId")+""));
        course.setCourseName(map.get("courseName")+"");
        //二次查询老师信息设置关联
        Integer tid = Integer.parseInt(map.get("tid")+"");
        Teacher teacher =DaoFactory.getInstance().getTeacherDao().findById(tid);
        course.setTeacher(teacher);
        return course;
    }
}
